{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d8f76480-b620-4cfa-bcaa-236e667ae45b",
   "metadata": {},
   "source": [
    "The following example is adapted from our Search Labs post on using [ES|QL with the Python language client](https://www.elastic.co/search-labs/blog/esql-pandas-dataframes-python). You can load this data using a method described in this [blog post](https://www.elastic.co/search-labs/blog/analyzing-data-using-python-elasticsearch-and-kibana)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "800448a5-57e9-4392-bdbe-09b889a87848",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import required libraries\n",
    "from io import StringIO  # Used to convert strings to file-like objects\n",
    "from getpass import getpass  # Securely request password or sensitive input\n",
    "from elasticsearch import (\n",
    "    Elasticsearch,\n",
    ")  # Elasticsearch client from the official library\n",
    "import pandas as pd  # Pandas library for data manipulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2dd890b9-828a-49ee-bbdf-c3de1a5576d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Host:  ········\n",
      "API Key:  ········\n"
     ]
    }
   ],
   "source": [
    "# Create an Elasticsearch client instance\n",
    "client = Elasticsearch(\n",
    "    getpass(\"Host: \"),  # Securely get the host for the Elasticsearch cluster\n",
    "    api_key=getpass(\"API Key: \"),  # Securely get the API key for authentication\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ed7a849a-b23e-4a18-9377-04a790df60e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Perform a query to retrieve the first 500 entries\n",
    "response = client.esql.query(\n",
    "    query=\"FROM esql | LIMIT 500\",\n",
    "    format=\"csv\",  # Specify the output format as CSV\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4e152565-84d8-4632-86ff-9c8244aeef71",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AST</th>\n",
       "      <th>BLK</th>\n",
       "      <th>DREB</th>\n",
       "      <th>FG3A</th>\n",
       "      <th>FG3M</th>\n",
       "      <th>FG3_PCT</th>\n",
       "      <th>FGA</th>\n",
       "      <th>FGM</th>\n",
       "      <th>FG_PCT</th>\n",
       "      <th>FTA</th>\n",
       "      <th>...</th>\n",
       "      <th>SEASON_ID.keyword</th>\n",
       "      <th>STL</th>\n",
       "      <th>TEAM_ABBREVIATION</th>\n",
       "      <th>TEAM_ABBREVIATION.keyword</th>\n",
       "      <th>TEAM_ID</th>\n",
       "      <th>TEAM_NAME</th>\n",
       "      <th>TEAM_NAME.keyword</th>\n",
       "      <th>TOV</th>\n",
       "      <th>WL</th>\n",
       "      <th>WL.keyword</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21</td>\n",
       "      <td>3</td>\n",
       "      <td>35</td>\n",
       "      <td>37</td>\n",
       "      <td>14</td>\n",
       "      <td>0.378</td>\n",
       "      <td>86</td>\n",
       "      <td>36</td>\n",
       "      <td>0.419</td>\n",
       "      <td>18</td>\n",
       "      <td>...</td>\n",
       "      <td>42023</td>\n",
       "      <td>5</td>\n",
       "      <td>BOS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1610612738</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>10</td>\n",
       "      <td>W</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>33</td>\n",
       "      <td>37</td>\n",
       "      <td>11</td>\n",
       "      <td>0.297</td>\n",
       "      <td>82</td>\n",
       "      <td>39</td>\n",
       "      <td>0.476</td>\n",
       "      <td>21</td>\n",
       "      <td>...</td>\n",
       "      <td>42023</td>\n",
       "      <td>4</td>\n",
       "      <td>BOS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1610612738</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>5</td>\n",
       "      <td>W</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>8</td>\n",
       "      <td>33</td>\n",
       "      <td>32</td>\n",
       "      <td>12</td>\n",
       "      <td>0.375</td>\n",
       "      <td>80</td>\n",
       "      <td>37</td>\n",
       "      <td>0.463</td>\n",
       "      <td>21</td>\n",
       "      <td>...</td>\n",
       "      <td>42023</td>\n",
       "      <td>9</td>\n",
       "      <td>BOS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1610612738</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>12</td>\n",
       "      <td>L</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27</td>\n",
       "      <td>6</td>\n",
       "      <td>34</td>\n",
       "      <td>49</td>\n",
       "      <td>22</td>\n",
       "      <td>0.449</td>\n",
       "      <td>82</td>\n",
       "      <td>39</td>\n",
       "      <td>0.476</td>\n",
       "      <td>16</td>\n",
       "      <td>...</td>\n",
       "      <td>42023</td>\n",
       "      <td>6</td>\n",
       "      <td>BOS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1610612738</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>10</td>\n",
       "      <td>W</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>29</td>\n",
       "      <td>15</td>\n",
       "      <td>38</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "      <td>0.500</td>\n",
       "      <td>89</td>\n",
       "      <td>51</td>\n",
       "      <td>0.573</td>\n",
       "      <td>21</td>\n",
       "      <td>...</td>\n",
       "      <td>22023</td>\n",
       "      <td>10</td>\n",
       "      <td>BOS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1610612738</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>14</td>\n",
       "      <td>W</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>40</td>\n",
       "      <td>45</td>\n",
       "      <td>15</td>\n",
       "      <td>0.333</td>\n",
       "      <td>90</td>\n",
       "      <td>43</td>\n",
       "      <td>0.478</td>\n",
       "      <td>27</td>\n",
       "      <td>...</td>\n",
       "      <td>22023</td>\n",
       "      <td>4</td>\n",
       "      <td>BOS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1610612738</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>11</td>\n",
       "      <td>W</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>27</td>\n",
       "      <td>2</td>\n",
       "      <td>46</td>\n",
       "      <td>35</td>\n",
       "      <td>20</td>\n",
       "      <td>0.571</td>\n",
       "      <td>95</td>\n",
       "      <td>54</td>\n",
       "      <td>0.568</td>\n",
       "      <td>28</td>\n",
       "      <td>...</td>\n",
       "      <td>22023</td>\n",
       "      <td>5</td>\n",
       "      <td>BOS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1610612738</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>11</td>\n",
       "      <td>W</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>36</td>\n",
       "      <td>53</td>\n",
       "      <td>19</td>\n",
       "      <td>0.358</td>\n",
       "      <td>102</td>\n",
       "      <td>51</td>\n",
       "      <td>0.500</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>22023</td>\n",
       "      <td>11</td>\n",
       "      <td>BOS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1610612738</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>17</td>\n",
       "      <td>W</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>20</td>\n",
       "      <td>6</td>\n",
       "      <td>39</td>\n",
       "      <td>39</td>\n",
       "      <td>16</td>\n",
       "      <td>0.410</td>\n",
       "      <td>95</td>\n",
       "      <td>45</td>\n",
       "      <td>0.474</td>\n",
       "      <td>19</td>\n",
       "      <td>...</td>\n",
       "      <td>22023</td>\n",
       "      <td>7</td>\n",
       "      <td>BOS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1610612738</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>15</td>\n",
       "      <td>W</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>18</td>\n",
       "      <td>11</td>\n",
       "      <td>39</td>\n",
       "      <td>39</td>\n",
       "      <td>12</td>\n",
       "      <td>0.308</td>\n",
       "      <td>77</td>\n",
       "      <td>37</td>\n",
       "      <td>0.481</td>\n",
       "      <td>26</td>\n",
       "      <td>...</td>\n",
       "      <td>22023</td>\n",
       "      <td>6</td>\n",
       "      <td>BOS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1610612738</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>13</td>\n",
       "      <td>W</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>85 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    AST  BLK  DREB  FG3A  FG3M  FG3_PCT  FGA  FGM  FG_PCT  FTA  ...  \\\n",
       "0    21    3    35    37    14    0.378   86   36   0.419   18  ...   \n",
       "1    22    6    33    37    11    0.297   82   39   0.476   21  ...   \n",
       "2    21    8    33    32    12    0.375   80   37   0.463   21  ...   \n",
       "3    27    6    34    49    22    0.449   82   39   0.476   16  ...   \n",
       "4    29   15    38    32    16    0.500   89   51   0.573   21  ...   \n",
       "..  ...  ...   ...   ...   ...      ...  ...  ...     ...  ...  ...   \n",
       "80   22    6    40    45    15    0.333   90   43   0.478   27  ...   \n",
       "81   27    2    46    35    20    0.571   95   54   0.568   28  ...   \n",
       "82   31    6    36    53    19    0.358  102   51   0.500    7  ...   \n",
       "83   20    6    39    39    16    0.410   95   45   0.474   19  ...   \n",
       "84   18   11    39    39    12    0.308   77   37   0.481   26  ...   \n",
       "\n",
       "    SEASON_ID.keyword  STL TEAM_ABBREVIATION  TEAM_ABBREVIATION.keyword  \\\n",
       "0               42023    5               BOS                        BOS   \n",
       "1               42023    4               BOS                        BOS   \n",
       "2               42023    9               BOS                        BOS   \n",
       "3               42023    6               BOS                        BOS   \n",
       "4               22023   10               BOS                        BOS   \n",
       "..                ...  ...               ...                        ...   \n",
       "80              22023    4               BOS                        BOS   \n",
       "81              22023    5               BOS                        BOS   \n",
       "82              22023   11               BOS                        BOS   \n",
       "83              22023    7               BOS                        BOS   \n",
       "84              22023    6               BOS                        BOS   \n",
       "\n",
       "       TEAM_ID       TEAM_NAME TEAM_NAME.keyword  TOV  WL  WL.keyword  \n",
       "0   1610612738  Boston Celtics    Boston Celtics   10   W           W  \n",
       "1   1610612738  Boston Celtics    Boston Celtics    5   W           W  \n",
       "2   1610612738  Boston Celtics    Boston Celtics   12   L           L  \n",
       "3   1610612738  Boston Celtics    Boston Celtics   10   W           W  \n",
       "4   1610612738  Boston Celtics    Boston Celtics   14   W           W  \n",
       "..         ...             ...               ...  ...  ..         ...  \n",
       "80  1610612738  Boston Celtics    Boston Celtics   11   W           W  \n",
       "81  1610612738  Boston Celtics    Boston Celtics   11   W           W  \n",
       "82  1610612738  Boston Celtics    Boston Celtics   17   W           W  \n",
       "83  1610612738  Boston Celtics    Boston Celtics   15   W           W  \n",
       "84  1610612738  Boston Celtics    Boston Celtics   13   W           W  \n",
       "\n",
       "[85 rows x 34 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the CSV response into a pandas DataFrame\n",
    "df = pd.read_csv(StringIO(response.body))\n",
    "df  # Display the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ae173c10-94f5-4608-ba63-9bd37ca84378",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query to fetch game dates and win/loss information, limiting to 10 entries\n",
    "wl_date = client.esql.query(\n",
    "    query=\"FROM esql | KEEP GAME_DATE, WL | LIMIT 10\",\n",
    "    format=\"csv\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a0cfd315-8d25-44aa-bc70-19efa32bf5b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GAME_DATE</th>\n",
       "      <th>WL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-04-29T00:00:00.000Z</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-04-27T00:00:00.000Z</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-04-24T00:00:00.000Z</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-04-21T00:00:00.000Z</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-04-14T00:00:00.000Z</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2024-04-12T00:00:00.000Z</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2024-04-11T00:00:00.000Z</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2024-04-07T00:00:00.000Z</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2024-04-05T00:00:00.000Z</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2024-04-03T00:00:00.000Z</td>\n",
       "      <td>W</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  GAME_DATE WL\n",
       "0  2024-04-29T00:00:00.000Z  W\n",
       "1  2024-04-27T00:00:00.000Z  W\n",
       "2  2024-04-24T00:00:00.000Z  L\n",
       "3  2024-04-21T00:00:00.000Z  W\n",
       "4  2024-04-14T00:00:00.000Z  W\n",
       "5  2024-04-12T00:00:00.000Z  W\n",
       "6  2024-04-11T00:00:00.000Z  L\n",
       "7  2024-04-07T00:00:00.000Z  W\n",
       "8  2024-04-05T00:00:00.000Z  W\n",
       "9  2024-04-03T00:00:00.000Z  W"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the query response into a DataFrame\n",
    "wl_date_df = pd.read_csv(StringIO(wl_date.body))\n",
    "wl_date_df  # Display the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a422a04a-a314-4a45-8c09-73aadfb958bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query to calculate average field goal percentage and average three-point percentage\n",
    "stats = client.esql.query(\n",
    "    query=\"FROM esql | STATS AVG(FG_PCT), AVG(FG3_PCT) | LIMIT 1\",\n",
    "    format=\"csv\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1468ab81-5317-4049-b9e9-5ee30519f194",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AVG(FG_PCT)</th>\n",
       "      <th>AVG(FG3_PCT)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.487341</td>\n",
       "      <td>0.387706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AVG(FG_PCT)  AVG(FG3_PCT)\n",
       "0     0.487341      0.387706"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the stats query response into a DataFrame\n",
    "stats_df = pd.read_csv(StringIO(stats.body))\n",
    "stats_df  # Display the DataFrame"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
